package com.qiya.LiaoDa.kb;



import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;


public class DBOperate {
    private MySQLiteAccess mySQLiteAccess;
    private SQLiteDatabase database;

    public void test(Context context){
        mySQLiteAccess=new MySQLiteAccess(context,3);
        database=mySQLiteAccess.getReadableDatabase();
    }
    public void insert(SP s){
        database.execSQL("insert into sp values(?,?,?,?)",new Object[] {s.getId(),s.getName(),s.getSex(),s.getAge()});
        System.out.println("插入数据成功");
    }
    public void delete(int id){
        Integer I=new Integer(id);
        database.execSQL("delete from sp where id=?",new Object[]{I});
    }
    public List<SP> searchByName(String key){
        List<SP> listByName=new ArrayList<SP>();
        key="%"+key+"%";
        Cursor cursor=database.rawQuery("select * from student where name like?",new String[]{key});
        while(cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String name=cursor.getString(cursor.getColumnIndex("name"));
            String sex=cursor.getString(cursor.getColumnIndex("sex"));
            int age=cursor.getInt(cursor.getColumnIndex("age"));
            listByName.add(new SP(id,name,sex,age));
        }
        return listByName;
    }

    public List<SP> queryAll(){
        List<SP> stus=new ArrayList<SP>();
        Cursor cursor=database.rawQuery("select * from sp",null);
        while(cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String name=cursor.getString(cursor.getColumnIndex("name"));
            String sex=cursor.getString(cursor.getColumnIndex("sex"));
            int age=cursor.getInt(cursor.getColumnIndex("age"));
            stus.add(new SP(id,name,sex,age));
        }
        return stus;
    }

    public void update(SP stu) {
        database.execSQL("update sp set name=?,sex=?,age=? where id=?"
                ,new Object[] { stu.getName(), stu.getSex(), stu.getAge(),stu.getId() });
    }

    public List<SP> searchByAll(String key){
        List<SP> listByAll=new ArrayList<SP>();
        key="%"+key+"%";
        Cursor cursor=database.rawQuery("select * from sp where id like? or name like? or sex like? or age like?"
                ,new String[]{key,key,key,key});
        while(cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String name=cursor.getString(cursor.getColumnIndex("name"));
            String sex=cursor.getString(cursor.getColumnIndex("sex"));
            int age=cursor.getInt(cursor.getColumnIndex("age"));
            listByAll.add(new SP(id,name,sex,age));
        }
        return listByAll;
    }
}